1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Imports System.IO
4
5 Public Class frmBillingRecord
6
7 Public Sub Getdata()
8 Try
9 con = New SqlConnection(cs)
10 con.Open()
11 cmd = New SqlCommand("Select Inv_ID, RTRIM(InvoiceNo), InvoiceDate, Customer.ID,RTRIM(Customer.CustomerID),RTRIM(Name),RTRIM(ContactNo), GrandTotal, TotalPaid, Balance, RTRIM(InvoiceInfo.Remarks) from Customer,InvoiceInfo where Customer.ID=InvoiceInfo.CustomerID order by InvoiceDate", con)
12 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
13 dgw.Rows.Clear()
14 While (rdr.Read() = True)
15 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10))
16 End While
17 con.Close()
18 Catch ex As Exception
19 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
20 End Try
21 End Sub
22 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
23 Getdata()
24 fillInvoiceNo()
25 End Sub
26
27 Private Sub dgw_MouseClick(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
28 Try
29 If dgw.Rows.Count > 0 Then
30 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
31 frmBilling.Show()
32 Me.Hide()
33 frmBilling.txtID.Text = dr.Cells(0).Value.ToString()
34 frmBilling.txtInvoiceNo.Text = dr.Cells(1).Value.ToString()
35 frmBilling.dtpInvoiceDate.Text = dr.Cells(2).Value.ToString()
36 frmBilling.txtCustomerID.Text = dr.Cells(4).Value.ToString()
37 frmBilling.txtCID.Text = dr.Cells(3).Value.ToString()
38 frmBilling.txtCustomerName.Text = dr.Cells(5).Value.ToString()
39 frmBilling.txtContactNo.Text = dr.Cells(6).Value.ToString()
40 frmBilling.txtGrandTotal.Text = dr.Cells(7).Value.ToString()
41 frmBilling.txtTotalPayment.Text = dr.Cells(8).Value.ToString()
42 frmBilling.txtPaymentDue.Text = dr.Cells(9).Value.ToString()
43 frmBilling.txtRemarks.Text = dr.Cells(10).Value.ToString()
44 frmBilling.btnSave.Enabled = False
45 frmBilling.btnUpdate.Enabled = True
46 frmBilling.btnPrint.Enabled = True
47 frmBilling.btnDelete.Enabled = True
48 frmBilling.lblSet.Text = "Not Allowed"
49 frmBilling.btnAdd.Enabled = False
50 frmBilling.txtCustomerName.ReadOnly = True
51 frmBilling.txtContactNo.ReadOnly = True
52 con = New SqlConnection(cs)
53 con.Open()
54 Dim sql As String = "SELECT RTRIM(ProductCode),RTRIM(ProductName), Invoice_Product.CostPrice, Invoice_Product.SellingPrice, Invoice_Product.Margin, Invoice_Product.Qty, Invoice_Product.Amount, Invoice_Product.DiscountPer, Invoice_Product.Discount, Invoice_Product.VATPer, Invoice_Product.VAT, Invoice_Product.TotalAmount,Product.PID from InvoiceInfo,Invoice_Product,Product where InvoiceInfo.Inv_ID=Invoice_Product.InvoiceID and Product.PID=Invoice_Product.ProductID and InvoiceInfo.Inv_ID=@d1"
55 cmd = New SqlCommand(sql, con)
56 cmd.Parameters.AddWithValue("@d1", dr.Cells(0).Value.ToString())
57 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
58 frmBilling.DataGridView1.Rows.Clear()
59 While (rdr.Read() = True)
60 frmBilling.DataGridView1.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12))
61 End While
62 con.Close()
63 con = New SqlConnection(cs)
64 con.Open()
65 Dim sql1 As String = "SELECT RTRIM(PaymentMode),Invoice_Payment.TotalPaid,PaymentDate from InvoiceInfo,Invoice_Payment where InvoiceInfo.Inv_ID=Invoice_Payment.InvoiceID and InvoiceInfo.Inv_ID=@d1"
66 cmd = New SqlCommand(sql1, con)
67 cmd.Parameters.AddWithValue("@d1", dr.Cells(0).Value.ToString())
68 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
69 frmBilling.DataGridView2.Rows.Clear()
70 While (rdr.Read() = True)
71 frmBilling.DataGridView2.Rows.Add(rdr(0), rdr(1), rdr(2))
72 End While
73 con.Close()
74 con = New SqlConnection(cs)
75 con.Open()
76 Dim ct As String = "select RTRIM(CustomerType) from Customer where ID=" & dr.Cells(3).Value & ""
77 cmd = New SqlCommand(ct)
78 cmd.Connection = con
79 rdr = cmd.ExecuteReader()
80 If rdr.Read Then
81 frmBilling.txtCustomerType.Text = rdr.GetValue(0)
82 If Not rdr Is Nothing Then
83 rdr.Close()
84 End If
85 Exit Sub
86 End If
87 con.Close()
88
89 End If
90 Catch ex As Exception
91 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
92 End Try
93 End Sub
94
95 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
96 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
97 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
98 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
99 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
100 End If
101 Dim b As Brush = SystemBrushes.ControlText
102 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
103
104 End Sub
105 Sub fillInvoiceNo()
106 Try
107 con = New SqlConnection(cs)
108 con.Open()
109 adp = New SqlDataAdapter()
110 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(InvoiceNo) FROM InvoiceInfo", con)
111 ds = New DataSet("ds")
112 adp.Fill(ds)
113 dtable = ds.Tables(0)
114 cmbInvoiceNo.Items.Clear()
115 For Each drow As DataRow In dtable.Rows
116 cmbInvoiceNo.Items.Add(drow(0).ToString())
117 Next
118 Catch ex As Exception
119 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
120 End Try
121 End Sub
122 Sub Reset()
123 cmbInvoiceNo.Text = ""
124 txtCustomerName.Text = ""
125 fillInvoiceNo()
126 dtpDateFrom.Text = Today
127 dtpDateTo.Text = Today
128 DateTimePicker2.Text = Today
129 DateTimePicker1.Text = Today
130 Getdata()
131 End Sub
132 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
133 Reset()
134 End Sub
135
136 Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
137 Me.Close()
138 End Sub
139
140
141 Private Sub btnExportExcel_Click(sender As System.Object, e As System.EventArgs) Handles btnExportExcel.Click
142 Dim rowsTotal, colsTotal As Short
143 Dim I, j, iC As Short
144 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
145 Dim xlApp As New Excel.Application
146 Try
147 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
148 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
149 xlApp.Visible = True
150
151 rowsTotal = dgw.RowCount
152 colsTotal = dgw.Columns.Count - 1
153 With excelWorksheet
154 .Cells.Select()
155 .Cells.Delete()
156 For iC = 0 To colsTotal
157 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
158 Next
159 For I = 0 To rowsTotal - 1
160 For j = 0 To colsTotal
161 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
162 Next j
163 Next I
164 .Rows("1:1").Font.FontStyle = "Bold"
165 .Rows("1:1").Font.Size = 12
166
167 .Cells.Columns.AutoFit()
168 .Cells.Select()
169 .Cells.EntireColumn.AutoFit()
170 .Cells(1, 1).Select()
171 End With
172 Catch ex As Exception
173 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
174 Finally
175 'RELEASE ALLOACTED RESOURCES
176 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
177 xlApp = Nothing
178 End Try
179 End Sub
180
181 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
182 Try
183 con = New SqlConnection(cs)
184 con.Open()
185 cmd = New SqlCommand("Select Inv_ID, RTRIM(InvoiceNo), InvoiceDate, Customer.ID,RTRIM(Customer.CustomerID),RTRIM(Name),RTRIM(ContactNo), GrandTotal, TotalPaid, Balance, RTRIM(InvoiceInfo.Remarks) from Customer,InvoiceInfo where Customer.ID=InvoiceInfo.CustomerID and InvoiceDate between @d1 and @d2 order by InvoiceDate", con)
186 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
187 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
188 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
189 dgw.Rows.Clear()
190 While (rdr.Read() = True)
191 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10))
192 End While
193 con.Close()
194 Catch ex As Exception
195 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
196 End Try
197 End Sub
198
199 Private Sub cmbOrderNo_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbInvoiceNo.SelectedIndexChanged
200 Try
201 con = New SqlConnection(cs)
202 con.Open()
203 cmd = New SqlCommand("Select Inv_ID, RTRIM(InvoiceNo), InvoiceDate, Customer.ID,RTRIM(Customer.CustomerID),RTRIM(Name),RTRIM(ContactNo), GrandTotal, TotalPaid, Balance, RTRIM(InvoiceInfo.Remarks) from Customer,InvoiceInfo where Customer.ID=InvoiceInfo.CustomerID and InvoiceNo='" & cmbInvoiceNo.Text & "' order by InvoiceDate", con)
204 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
205 dgw.Rows.Clear()
206 While (rdr.Read() = True)
207 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10))
208 End While
209 con.Close()
210 Catch ex As Exception
211 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
212 End Try
213 End Sub
214
215 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
216 Try
217 con = New SqlConnection(cs)
218 con.Open()
219 cmd = New SqlCommand("Select Inv_ID, RTRIM(InvoiceNo), InvoiceDate, Customer.ID,RTRIM(Customer.CustomerID),RTRIM(Name),RTRIM(ContactNo), GrandTotal, TotalPaid, Balance, RTRIM(InvoiceInfo.Remarks) from Customer,InvoiceInfo where Customer.ID=InvoiceInfo.CustomerID and InvoiceDate between @d1 and @d2 and Balance > 0 order by InvoiceDate", con)
220 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = DateTimePicker2.Value.Date
221 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = DateTimePicker1.Value.Date
222 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
223 dgw.Rows.Clear()
224 While (rdr.Read() = True)
225 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10))
226 End While
227 con.Close()
228 Catch ex As Exception
229 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
230 End Try
231 End Sub
232
233 Private Sub txtCustomerName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtCustomerName.TextChanged
234 Try
235 con = New SqlConnection(cs)
236 con.Open()
237 cmd = New SqlCommand("Select Inv_ID, RTRIM(InvoiceNo), InvoiceDate, Customer.ID,RTRIM(Customer.CustomerID),RTRIM(Name),RTRIM(ContactNo), GrandTotal, TotalPaid, Balance, RTRIM(InvoiceInfo.Remarks) from Customer,InvoiceInfo where Customer.ID=InvoiceInfo.CustomerID and Name like '%" & txtCustomerName.Text & "%' order by InvoiceDate", con)
238 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
239 dgw.Rows.Clear()
240 While (rdr.Read() = True)
241 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10))
242 End While
243 con.Close()
244 Catch ex As Exception
245 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
246 End Try
247 End Sub
248
249 Private Sub cmbInvoiceNo_Format(sender As System.Object, e As System.Windows.Forms.ListControlConvertEventArgs) Handles cmbInvoiceNo.Format
250 If (e.DesiredType Is GetType(String)) Then
251 e.Value = e.Value.ToString.Trim
252 End If
253 End Sub
254 End Class
